﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    class BaseInfoCtrl
    {
        #region 基础信息管理

        #region 获取国家表数据
        /// <summary>
        ///  获取国家表数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCountryData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT CountryID,CountryName,CountryCode,EnglishName,FrenchName,OrderID FROM bas_Country ORDER BY OrderID DESC,CountryCode";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 更新国家表数据
        internal static string CreateCountryData(CountryEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_Country([CountryCode], [CountryName], [EnglishName], [FrenchName], [OrderID])
                                         VALUES('{0}','{1}','{2}','{3}',{4});Select SCOPE_IDENTITY();",
                                           entity.CountryCode,
                                           entity.CountryName,
                                           entity.EnglishName,
                                           entity.FrenchName,
                                           entity.OrderID);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
            //return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int UpdateCountryData(CountryEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE bas_Country SET [CountryCode]='{0}', [CountryName]='{1}', [EnglishName]='{2}', [FrenchName]='{3}', [OrderID]={4}
                                         WHERE CountryID={5}",
                                          entity.CountryCode,
                                          entity.CountryName,
                                          entity.EnglishName,
                                          entity.FrenchName,
                                          entity.OrderID,
                                          entity.CountryID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteCountryData(string countryId)
        {
            if (countryId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Country WHERE CountryID={0}", countryId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        #region 获取城市表数据
        /// <summary>
        ///  获取城市表数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCityData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT CityID,Name,EnglishName,FrenchName FROM bas_City ORDER BY EngLishName";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        ///  根据中文名称获取城市表数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCityDataByName(string name)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format(@"SELECT CityID,Name,EnglishName,FrenchName FROM bas_City WHERE Name='{0}'",name);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据输入城市名称拼音获取城市数据
        /// </summary>
        /// <param name="cityPY"></param>
        /// <returns></returns>
        public static DataTable GetCityData(string cityPY)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            dbCommand = db.GetSqlStringCommand(string.Format("SELECT CityID,Name,EnglishName,FrenchName FROM bas_City WHERE (lower(dbo.f_getpinyin(Name)) LIKE '{0}%' OR lower(dbo.f_getpy(Name)) LIKE '{0}%') ORDER BY EngLishName ",SqlHelper.SQLCheck(cityPY)));

            DataTable dt = new DataTable();
            dt.Load(db.ExecuteReader(dbCommand));
            return dt;
        }

        #endregion

        #region 更新城市表数据

        internal static string CreateCityData(CityEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_City ([Name], [EnglishName], [FrenchName])
                                         VALUES('{0}','{1}', '{2}');Select SCOPE_IDENTITY();",
                                           entity.Name,
                                           entity.EnglishName,
                                           entity.FrenchName);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
            //return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int UpdateCityData(CityEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE bas_City SET [Name]='{0}', [EnglishName]='{1}', [FrenchName]='{2}' WHERE CityID={3}",
                                            entity.Name,
                                            entity.EnglishName,
                                            entity.FrenchName,
                                            entity.CityID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteCityData(string cityId)
        {
            if (cityId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_City WHERE CityID={0}", cityId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 获取客户表数据
        /// <summary>
        ///  获取客户表数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCustomerData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT [CustomerID], [Country], [CustomerName], [Telephone], [Fax], [EMail], [Bank], [Accounts],[IsSigned], [Remark],[CreditLimit] FROM bas_Customer";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        /// <summary>
        /// 检测是否存在同名ID
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns>true存在，false不存在</returns>
        internal static bool CheckExistSameCustomerID(string customerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            dbCommand = db.GetSqlStringCommand(string.Format("SELECT CustomerID FROM bas_Customer WHERE CustomerID='{0}'", customerId.Trim()));
            object obj = db.ExecuteScalar(dbCommand);

            return obj != null;
        }

        #region 增加、删除、更新客户表数据

        internal static string CreateCustomerData(CustomerEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_Customer([Country], [CustomerName], [Telephone], [Fax], [EMail], [Bank], [Accounts], [Remark],[CustomerID],[IsSigned],[CreditLimit])
                                         VALUES('{0}', '{1}', '{2}', '{3}', '{4}','{5}','{6}', '{7}','{8}','{9}',{10});Select SCOPE_IDENTITY();",
                                                                   entity.Country,
                                                                   entity.CustomerName,
                                                                   entity.Telephone,
                                                                   entity.Fax,
                                                                   entity.EMail,
                                                                   entity.Bank,
                                                                   entity.Accounts,
                                                                   entity.Remark,
                                                                   entity.CustomerID,
                                                                   entity.IsSigned,
                                                                   entity.CreditLimit);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        internal static int UpdateCustomerData(CustomerEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE bas_Customer SET [Country]='{0}', [CustomerName]='{1}', [Telephone]='{2}', [Fax]='{3}', [EMail]='{4}', [Bank]='{5}', [Accounts]='{6}', [Remark]='{7}',[IsSigned]={8},[CreditLimit]={9} 
                                         WHERE CustomerID='{10}'",
                                           entity.Country,
                                           entity.CustomerName,
                                           entity.Telephone,
                                           entity.Fax,
                                           entity.EMail,
                                           entity.Bank,
                                           entity.Accounts,
                                           entity.Remark,
                                           entity.IsSigned,
                                           entity.CreditLimit,
                                           entity.CustomerID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteCustomerData(string customerId)
        {
            if (customerId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Customer WHERE CustomerID={0}", customerId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        #region 获取供应商信息

        /// <summary>
        /// 根据团计划获取计划中的供应商信息
        /// </summary>
        /// <param name="providerNamePY"></param>
        /// <returns></returns>
        internal static DataTable GetProviderDataForSchedule(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT  p.[City], p.[ProviderName],p.[Contact],p.[Email] FROM orgt_TourGroupSchedule t,bas_Provider p
                                                                        WHERE TourgroupID='{0}' AND (t.TravelAgencyID=p.ProviderID OR t.HotelID=p.ProviderID)", tourgroupId));

            DataTable dt = new DataTable();
            dt.Load(db.ExecuteReader(dbCommand));
            return dt;

        }

        internal static DataTable GetProviderData(string providerNamePY)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT  [ProviderID], [City], [ProviderType], [FullName], [ProviderName], [EnglishName], [Address], [EnglishAddress], [PostCode], [Contact], [Telephone], [Fax], [Email],[IsSigned],[IsLocalProvider],[AccountBank],[Account],[AccountName],[UserName],[Password]  FROM bas_Provider
                               WHERE lower(dbo.f_getpinyin(ProviderName)) LIKE '{0}%' OR lower(dbo.f_getpy(ProviderName)) LIKE '{0}%'", SqlHelper.SQLCheck(providerNamePY)));

            DataTable dt = new DataTable();
            dt.Load(db.ExecuteReader(dbCommand));
            return dt;

        }
        /// <summary>
        /// 获取供应商信息
        /// </summary>
        /// <param name="type">供应商类型</param>
        /// <returns></returns>
        internal static DataTable GetProviderData(ProviderTypeEnum[] types)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder formatSql = new StringBuilder();
            formatSql.Append(@"SELECT  [ProviderID], [ProviderType], [FullName], [ProviderName], [EnglishName], [Address], [EnglishAddress], [PostCode], [Telephone], [fax], [email],[IsSigned],[IsLocalProvider],[AccountBank],[Account],[AccountName]  FROM bas_Provider WHERE ");

            for (int i = 0; i < types.Length; i++)
            {
                formatSql.AppendFormat(" ProviderType like '%{0}%' ", types[i]);

                if (i != types.Length - 1)
                    formatSql.Append(" OR ");
            }
            formatSql.AppendFormat(" ORDER BY [ProviderName] ");

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                DataRow tmpDr = ds.Tables[0].NewRow();
                tmpDr["ProviderID"] = DBNull.Value;
                tmpDr["ProviderName"] = "";
                ds.Tables[0].Rows.InsertAt(tmpDr, 0);
                return ds.Tables[0];
            }
            else
                return null;
        }
        /// <summary>
        /// 获取供应商信息
        /// </summary>
        /// <param name="type">供应商类型</param>
        /// <returns></returns>
        //internal static DataTable GetProviderData(string cityName,string providerType)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    string szCondition = "";
        //    StringBuilder formatSql = new StringBuilder();

        //    formatSql.Append(@"SELECT  [ProviderID], [City], [ProviderType], [FullName], [ProviderName], [EnglishName], [FrenchName], [Address], [EnglishAddress], [FrenchAddress], [PostCode], [Contact], [Telephone], [fax], [email],[IsSigned],[IsLocalProvider]  FROM bas_Provider ");

        //    if (providerType != "")
        //        szCondition = string.Format(" ProviderType like '%{0}%' ", providerType);

        //    if (szCondition != "")
        //    {
        //        if (cityName != "")
        //            szCondition += string.Format(" AND City like '%{0}%' ", cityName);
        //    }
        //    else
        //    {
        //        if (cityName != "")
        //            szCondition += string.Format(" City like '%{0}%' ", cityName);
        //    }

        //    if (szCondition != "")
        //        formatSql.Append(" WHERE " + szCondition);

        //    DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //    {
        //        DataRow tmpDr = ds.Tables[0].NewRow();
        //        tmpDr["ProviderID"] = DBNull.Value;
        //        tmpDr["ProviderName"] = "";
        //        ds.Tables[0].Rows.InsertAt(tmpDr, 0);
        //        return ds.Tables[0];
        //    }
        //    else
        //        return null;
        //}
        /// <summary>
        /// 判断是否是本地供应商
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static bool GetProviderForIsLocalProvider(string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT IsLocalProvider  FROM bas_Provider WHERE ProviderID={0}", providerId);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            object obj = db.ExecuteScalar(dbCommand);
            
            if (obj == null) 
                return false;
            if ( obj.ToString() == "")
                return false;
            return bool.Parse(obj.ToString());
        }
        /// <summary>
        /// 判断是否是"旅行社"类型供应商
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static bool GetProviderForIsTravelProvider(string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT count(*)  FROM bas_Provider WHERE ProviderID={0} AND ProviderType='{1}'", providerId, ProviderTypeEnum.旅行社);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            object obj = db.ExecuteScalar(dbCommand);

            return int.Parse(obj.ToString()) > 0 ? true : false;

        }
        /// <summary>
        /// 获取供应商信息
        /// </summary>
        /// <param name="providerId">供应商ID</param>
        /// <returns></returns>
        internal static ProviderEntity GetProviderDataByID(string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT  [ProviderID], [City], [ProviderType], [FullName], [ProviderName], [EnglishName], [Address], [EnglishAddress], [PostCode], [Contact], [Telephone], [fax], [email],[IsSigned],[IsLocalProvider],[AccountBank],[Account],[AccountName]  FROM bas_Provider WHERE ProviderID={0}", providerId);

            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            IDataReader reader = db.ExecuteReader(dbCommand);//.ExecuteDataSet(dbCommand);
            if (reader.Read())
            {
                ProviderEntity providerEntity = new ProviderEntity()
                {
                    ProviderID = int.Parse(reader["ProviderID"].ToString()),
                    City = reader["City"].ToString(),
                    ProviderType = reader["ProviderType"].ToString(),
                    FullName = reader["FullName"].ToString(),
                    ProviderName = reader["ProviderName"].ToString(),
                    EnglishName = reader["EnglishName"].ToString(),
                    Address = reader["Address"].ToString(),
                    EnglishAddress = reader["EnglishAddress"].ToString(),
                    PostCode = reader["PostCode"].ToString(),
                    Contact = reader["Contact"].ToString(),
                    Telephone = reader["Telephone"].ToString(),
                    fax = reader["fax"].ToString(),
                    email = reader["email"].ToString(),
                    IsSigned = reader["IsSigned"].ToString() == "1" ? 1 : 0,
                    IsLocalProvider = reader["IsLocalProvider"].ToString() == "1" ? 1 : 0,
                    AccountBank = reader["AccountBank"].ToString(),
                    Account = reader["Account"].ToString(),
                    AccountName = reader["AccountName"].ToString()
                };
                return providerEntity;
            }
            else
                return null;
        }
        /// <summary>
        /// 根据城市拼音和供应商拼音获取供应商数据
        /// </summary>
        /// <param name="providerTypes"></param>
        /// <param name="cityPY"></param>
        /// <param name="providerNamePY"></param>
        /// <returns></returns>
        internal static DataTable GetProviderData(ProviderTypeEnum[] providerTypes, string cityPY, string providerNamePY)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder formatSql = new StringBuilder();

            formatSql.AppendFormat(@"SELECT DISTINCT [ProviderID], [City], [ProviderType], [FullName], [ProviderName], [EnglishName], [Address], [EnglishAddress], [PostCode], [Contact], [Telephone], [fax], [email],[IsSigned],[IsLocalProvider],[AccountBank],[Account],[AccountName]   
                                                                         FROM bas_Provider WHERE (lower(dbo.f_getpinyin(City)) like '{0}%' OR lower(dbo.f_getpy(City)) like '{0}%') 
                                                                         AND (lower(dbo.f_getpinyin(ProviderName)) like '{1}%' OR lower(dbo.f_getpy(ProviderName)) like '{1}%') ", SqlHelper.SQLCheck(cityPY), SqlHelper.SQLCheck(providerNamePY));


            for (int i = 0; i < providerTypes.Length; i++)
            {
                if (i == 0)
                    formatSql.Append("AND (");

                formatSql.AppendFormat(" ProviderType like '%{0}%' ", providerTypes[i]);

                if (i != providerTypes.Length - 1)
                    formatSql.Append(" OR ");
            }
            formatSql.Append(")");
            DbCommand dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataTable dt = new DataTable();
            dt.Load(db.ExecuteReader(dbCommand));
            return dt;
        }
        /// <summary>
        /// 根据ID获取供应商名称
        /// </summary>
        /// <returns></returns>
        public static string GetTeamProviderNameById(string providerid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT ProviderName FROM bas_Provider where ProviderID={0}", providerid);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        /// <summary>
        /// 根据ID获取供应商类型
        /// </summary>
        /// <returns></returns>
        public static string GetProviderTypeById(int providerid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT ProviderType FROM bas_Provider where ProviderID={0}", providerid);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }

        #endregion

        #region 更新供应商基本信息

        internal static string CreateProviderData(ProviderEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_Provider([City], [ProviderType], [FullName], [ProviderName], [EnglishName], [Address], [EnglishAddress], [PostCode], [Contact], [Telephone], [fax], [email],[IsSigned],[IsLocalProvider],[AccountBank],[Account],[UserName],[Password] )
                                         VALUES(@City, @ProviderType, @FullName, @ProviderName, @EnglishName, @Address, @EnglishAddress, @PostCode, @Contact, @Telephone, @fax, @email,@IsSigned,@IsLocalProvider, @AccountBank,@Account,@UserName,@Password);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "Address", DbType.String, entity.Address);
            db.AddInParameter(dbCommand, "Contact", DbType.String, entity.Contact);
            db.AddInParameter(dbCommand, "EnglishAddress", DbType.String, entity.EnglishAddress);
            db.AddInParameter(dbCommand, "EnglishName", DbType.String, entity.EnglishName);
            db.AddInParameter(dbCommand, "fax", DbType.String, entity.fax);
            db.AddInParameter(dbCommand, "FullName", DbType.String, entity.FullName);
            db.AddInParameter(dbCommand, "PostCode", DbType.String, entity.PostCode);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ProviderType", DbType.String, entity.ProviderType);
            db.AddInParameter(dbCommand, "Telephone", DbType.String, entity.Telephone);
            db.AddInParameter(dbCommand, "email", DbType.String, entity.email);
            db.AddInParameter(dbCommand, "IsSigned", DbType.Int32, entity.IsSigned);
            db.AddInParameter(dbCommand, "IsLocalProvider", DbType.Int32, entity.IsLocalProvider);
            db.AddInParameter(dbCommand, "AccountBank", DbType.String, entity.AccountBank);
            db.AddInParameter(dbCommand, "Account", DbType.String, entity.Account);
            db.AddInParameter(dbCommand, "UserName", DbType.String, entity.UserName);
            db.AddInParameter(dbCommand, "Password", DbType.String, entity.Password);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateProviderData(ProviderEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE bas_Provider SET City = @city, ProviderType=@providerType, FullName=@fullName, ProviderName=@providerName, EnglishName=@englishName,
                                                   Address=@address, EnglishAddress=@englishAddress, PostCode=@postCode, Contact=@contact, Telephone=@telephone, fax=@fax, 
                                                   email=@email,IsSigned=@IsSigned,IsLocalProvider=@IsLocalProvider, AccountBank=@AccountBank,Account=@Account,UserName=@UserName,Password=@Password  WHERE ProviderID=@providerid";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "city", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "address", DbType.String, entity.Address);
            db.AddInParameter(dbCommand, "contact", DbType.String, entity.Contact);
            db.AddInParameter(dbCommand, "englishAddress", DbType.String, entity.EnglishAddress);
            db.AddInParameter(dbCommand, "englishName", DbType.String, entity.EnglishName);
            db.AddInParameter(dbCommand, "fax", DbType.String, entity.fax);
            db.AddInParameter(dbCommand, "fullName", DbType.String, entity.FullName);
            db.AddInParameter(dbCommand, "postCode", DbType.String, entity.PostCode);
            db.AddInParameter(dbCommand, "providerid", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "providerName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "providerType", DbType.String, entity.ProviderType);
            db.AddInParameter(dbCommand, "telephone", DbType.String, entity.Telephone);
            db.AddInParameter(dbCommand, "email", DbType.String, entity.email);
            db.AddInParameter(dbCommand, "IsSigned", DbType.Int32, entity.IsSigned);
            db.AddInParameter(dbCommand, "IsLocalProvider", DbType.Int32, entity.IsLocalProvider);
            db.AddInParameter(dbCommand, "AccountBank", DbType.String, entity.AccountBank);
            db.AddInParameter(dbCommand, "Account", DbType.String, entity.Account);
            db.AddInParameter(dbCommand, "UserName", DbType.String, entity.UserName);
            db.AddInParameter(dbCommand, "Password", DbType.String, entity.Password);
            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteProviderData(string providerId)
        {
            if (providerId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Provider WHERE ProviderID={0}", providerId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 获取导游表数据

        internal static object GetCiceroniPhoneById(string ciceroniId)
        {
            if (string.IsNullOrEmpty(ciceroniId)) return "";

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT [Phone] FROM bas_Ciceroni WHERE CiceroniID={0}", ciceroniId);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand);
        }
        
        /// <summary>
        /// 根据ID获取供应商名称
        /// </summary>
        /// <returns></returns>
        public static string GetCiceroniNameById(string ciceroniId)
        {
            if (string.IsNullOrEmpty(ciceroniId)) return "";

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT [CiceroniName] FROM bas_Ciceroni WHERE CiceroniID={0}", ciceroniId);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        
        //public static bool ValidateUser(string userame, string password)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = "SELECT [LockState],[UserName],[Password] FROM bas_Ciceroni WHERE UserName=@UserName AND Password=@Password";

        //    dbCommand = db.GetSqlStringCommand(sql);
        //    db.AddParameter(dbCommand, "@UserName", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, userame);
        //    db.AddParameter(dbCommand, "@Password", DbType.String, ParameterDirection.Input, "", DataRowVersion.Default, password);

        //    object obj = db.ExecuteScalar(dbCommand);
        //    if (obj == null)
        //        return false;
        //    else
        //        return !bool.Parse(obj.ToString());//如果是锁定状态则返回false否则返回true ，用锁定状态的相反状态表明验证成功与否

        //}

        /// <summary>
        /// 获取龙润的导游信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCiceroniData()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT [CiceroniID], [CiceroniName], [Sex], [Birthday], [CiceroniLanguage], [IDcardNo], [CiceroniNo], [Phone], [EMail], [Remark], [CiceroniLevel], [City],[OwnerProviderID],[UserName] FROM bas_Ciceroni  WHERE OwnerProviderID='0'";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取各旅行社的导游信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetCiceroniDataForTravel()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT c.[CiceroniID], c.[CiceroniName], c.[Sex], c.[Birthday], c.[CiceroniLanguage], c.[IDcardNo], c.[CiceroniNo], c.[Phone], c.[EMail], c.[Remark], c.[CiceroniLevel], c.[City],c.[OwnerProviderID],c.[UserName],c.[Name],p.ProviderName FROM bas_Ciceroni c,bas_Provider p WHERE c.OwnerProviderID=p.ProviderID AND OwnerProviderID<>'0'";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 从导游信息表中获取全部导游的语言信息
        /// </summary>
        /// <returns>只含‘CiceroniLanguage’字段的DataTable</returns>
        public static DataTable GetCiceroniLanguageData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT distinct [CiceroniLanguage] FROM bas_Ciceroni";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 从导游信息表中获取全部导游的姓名信息
        /// </summary>
        /// <returns>只含‘GetCiceroniNameData’字段的DataTable</returns>
        /*
        public static DataTable GetCiceroniNameData(string language)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            
            //去掉了sql中的distinct
            string sql = string.Format("SELECT [CiceroniName],[CiceroniID] FROM bas_Ciceroni WHERE CiceroniLanguage LIKE '%{0}%'  ORDER BY CiceroniLevel Desc", language);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
         */
        public static DataTable GetCiceroniNameData(string language)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            //去掉了sql中的distinct
            string sql = string.Format("SELECT [CiceroniName],[CiceroniID] FROM bas_Ciceroni WHERE CiceroniLanguage LIKE '%{0}%' AND OwnerProviderID= 0 ORDER BY CiceroniLevel Desc", language);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 更新导游表数据

        /// <summary>
        /// 创建一条导游信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static string CreateCiceroniData(CiceroniEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_Ciceroni ([CiceroniName], [Sex], [Birthday], [CiceroniLanguage], [IDcardNo], [CiceroniNo], 
                                                                   [Phone], [EMail], [Remark], [CiceroniLevel], [City],[OwnerProviderID])
                                                                   VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9},'{10}','{11}','{12}');Select SCOPE_IDENTITY();",
                                                                   entity.CiceroniName,
                                                                   entity.Sex,
                                                                   entity.Birthday,
                                                                   entity.CiceroniLanguage,
                                                                   entity.IDcardNo,
                                                                   entity.CiceroniNo,
                                                                   entity.Phone,
                                                                   entity.EMail,
                                                                   entity.Remark,
                                                                   entity.CiceroniLevel,
                                                                   entity.City,
                                                                   entity.OwnerProviderID,
                                                                   entity.UserName);
                                                                   //entity.Shortcut);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
            //return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新一条导游信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static int UpdateCiceroniData(CiceroniEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE bas_Ciceroni SET [CiceroniName]='{0}', [Sex]='{1}', [Birthday]='{2}', [CiceroniLanguage]='{3}',
                                                                 [IDcardNo]='{4}', [CiceroniNo]='{5}', [Phone]='{6}', [EMail]='{7}',
                                                                 [Remark]='{8}', [CiceroniLevel]={9}, [City]='{10}',[OwnerProviderID]='{11}',[UserName]='{12}' 
                                                                 WHERE CiceroniID={13}",//,[UserName]='{12}',[Password]='{13}',[LockState]={14} 
                                                                   entity.CiceroniName,
                                                                   entity.Sex,
                                                                   entity.Birthday,
                                                                   entity.CiceroniLanguage,
                                                                   entity.IDcardNo,
                                                                   entity.CiceroniNo,
                                                                   entity.Phone,
                                                                   entity.EMail,
                                                                   entity.Remark,
                                                                   entity.CiceroniLevel,
                                                                   entity.City,
                                                                   entity.OwnerProviderID,
                                                                   entity.UserName,
                                                                   //entity.UserName,
                                                                   //entity.Shortcut,
                                                                   //entity.Password,
                                                                   //entity.LockState,
                                                                   entity.CiceroniID);

            return db.ExecuteNonQuery(CommandType.Text, sql);

        }
        /// <summary>
        /// 删除指定ID的导游信息
        /// </summary>
        /// <param name="ciceroniId"></param>
        /// <returns></returns>
        internal static int DeleteCiceroniData(string ciceroniId)
        {
            if (ciceroniId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Ciceroni WHERE CiceroniID={0}", ciceroniId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 获取变更通知表数据
        /// <summary>
        ///  获取变更通知表数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetChangeNotifyData(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT [ChangeNotifyID], [TourgroupID],[Number],[ChangeTime], [Title], [Context] FROM orgt_ChangeNotify WHERE TourgroupID='{0}'", tourGroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 生成变更通知中‘计次数’
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        internal static string GetChangeNotifyMaxNumber(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("SELECT ISNULL(MAX([Number]),0)+1 FROM orgt_ChangeNotify WHERE TourgroupID='{0}'", tourGroupId);

            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        #endregion

        #region 更新变更通知表数据

        internal static string CreateChangeNotifyData(ChangeNotifyEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            //string numberSql = string.Format("SELECT ISNULL(MAX([Number]),0)+1 FROM orgt_ChangeNotify WHERE TourgroupID='{0}'", entity.TourgroupID);
            //string number = db.ExecuteScalar(CommandType.Text, numberSql).ToString();

            string sql = string.Format(@"INSERT INTO orgt_ChangeNotify([TourgroupID],[Number], [ChangeTime], [Title], [Context])
                                                                        VALUES('{0}',{1},'{2}','{3}','{4}');Select SCOPE_IDENTITY();",
                                                                   entity.TourgroupID,
                                                                   entity.Number,
                                                                   entity.ChangeTime,
                                                                   entity.Title,
                                                                   entity.Context);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        internal static int UpdateChangeNotifyData(ChangeNotifyEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_ChangeNotify SET [TourgroupID]='{0}', [ChangeTime]='{1}', [Title]='{2}', [Context]='{3}'
                                         WHERE ChangeNotifyID={4}", 
                                       entity.TourgroupID,
                                       entity.ChangeTime,
                                       entity.Title,
                                       entity.Context,
                                       entity.ChangeNotifyID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteChangeNotifyData(string changeNotifyId)
        {
            if (changeNotifyId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM orgt_ChangeNotify WHERE ChangeNotifyID={0}", changeNotifyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        #region 获取飞机航班基本信息

        /// <summary>
        /// 获取全部飞机航班信息
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetFlightData()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT [FlightID], [FlightCode], [StartTime], [EndTime], [BeginCity],[EndCity], [BeginPosition], [EndPosition], [PlaneModel] FROM [bas_Flight] ");
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取航班信息表
        /// </summary>
        /// <param name="StartCity"></param>
        /// <param name="EndCity"></param>
        /// <returns></returns>
        internal static DataTable GetFlightData(string startCityPY, string endCityPY)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT [FlightID], [FlightCode], [StartTime], [EndTime], [BeginCity],[EndCity],[BeginPosition], [EndPosition], [PlaneModel] FROM [bas_Flight] WHERE (lower(dbo.f_getpinyin(BeginCity)) LIKE '{0}%' OR lower(dbo.f_getpy(BeginCity)) LIKE '{0}%') 
                                         AND (lower(dbo.f_getpinyin(EndCity)) LIKE '{1}%' OR lower(dbo.f_getpy(EndCity)) LIKE '{1}%')", SqlHelper.SQLCheck(startCityPY), SqlHelper.SQLCheck(endCityPY)));

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        #region 更新飞机航班基本信息

        internal static string CreateFlightData(FlightEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_Flight([FlightCode], [StartTime], [EndTime], [BeginCity], [EndCity], [BeginPosition], [EndPosition], [PlaneModel])
                                         VALUES('{0}', '{1}','{2}','{3}','{4}','{5}','{6}','{7}');Select SCOPE_IDENTITY();",
                                           entity.FlightCode,
                                           entity.StartTime,
                                           entity.EndTime,
                                           entity.BeginCity,
                                           entity.EndCity,
                                           entity.BeginPosition,
                                           entity.EndPosition,
                                           entity.PlaneModel);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        internal static int UpdateFlightData(FlightEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE bas_Flight SET [FlightCode]='{0}', [StartTime]='{1}', [EndTime]='{2}', [BeginPosition]='{3}', [EndPosition]='{4}', [PlaneModel]='{5}',[BeginCity]='{6}', [EndCity]='{7}' 
                                         WHERE FlightID={8}",
                                           entity.FlightCode,
                                           entity.StartTime,
                                           entity.EndTime,
                                           entity.BeginPosition,
                                           entity.EndPosition,
                                           entity.PlaneModel,
                                           entity.BeginCity,
                                           entity.EndCity,
                                           entity.FlightID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteFlightData(string flightId)
        {
            if (flightId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Flight WHERE FlightID={0}", flightId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 获取火车列车时刻信息

        /// <summary>
        /// 获取全部列车时刻信息表
        /// </summary>
        /// <returns></returns>
        internal static DataTable GetTrainData()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT [TrainID], [TrainCode], [StartTime], [EndTime], [BeginPosition], [EndPosition], [TrainModel] FROM [bas_Train] ");

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 获取列车时刻信息表
        /// </summary>
        /// <param name="StartCity"></param>
        /// <param name="EndCity"></param>
        /// <returns></returns>
        internal static DataTable GetTrainData(string startCityPY, string endCityPY)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT [TrainID], [TrainCode], [StartTime], [EndTime], [BeginPosition], [EndPosition], [TrainModel] FROM [bas_Train] 
                                         WHERE (lower(dbo.f_getpinyin(BeginPosition)) LIKE '{0}%' OR lower(dbo.f_getpy(BeginPosition)) LIKE '{0}%') 
                                         AND (lower(dbo.f_getpinyin(EndPosition)) LIKE '{1}%' OR lower(dbo.f_getpy(EndPosition)) LIKE '{1}%')", SqlHelper.SQLCheck(startCityPY), SqlHelper.SQLCheck(endCityPY));

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 更新火车列车时刻信息

        internal static string CreateTrainData(TrainEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO bas_Train ([TrainCode], [StartTime], [EndTime], [BeginPosition], [EndPosition], [TrainModel])
                                         VALUES('{0}', '{1}','{2}','{3}','{4}','{5}');Select SCOPE_IDENTITY();",
                                           entity.TrainCode,
                                           entity.StartTime,
                                           entity.EndTime,
                                           entity.BeginPosition,
                                           entity.EndPosition,
                                           entity.TrainModel);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        internal static int UpdateTrainData(TrainEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE bas_Train SET [TrainCode]='{0}', [StartTime]='{1}', [EndTime]='{2}', [BeginPosition]='{3}', [EndPosition]='{4}', [TrainModel]='{5}'
                                         WHERE TrainID={6}",
                                           entity.TrainCode,
                                           entity.StartTime,
                                           entity.EndTime,
                                           entity.BeginPosition,
                                           entity.EndPosition,
                                           entity.TrainModel,
                                           entity.TrainID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteTrainData(string trainId)
        {
            if (trainId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Train WHERE TrainID={0}", trainId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        #region 获取部门信息

        /// <summary>
        /// 根据部门ID获取部门信息
        /// </summary>
        /// <param name="departmentId">大于等于1获取相应部门信息，否则返回全部</param>
        /// <returns></returns>
        public static DataTable GetDepartmentDataByDepartmentID(int departmentId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT * FROM bas_Department WHERE DepartmentID={0}", departmentId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }      
        /// <summary>
        /// 根据部门全部信息
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDepartmentData()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT * FROM bas_Department";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据所属级别获取部门信息0总经理,1部门领导,2普通员工
        /// </summary>
        /// <param name="rankId"></param>
        /// <returns></returns>
        public static DataTable GetDepartmentData(int rankId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "";
            switch (rankId)//否则根据当前登录用户级别进行显示
            {
                case 0://总经理 级别
                    sql = "SELECT * FROM bas_Department ";// 返回全部 
                    break;
                case 1://部门经理 级别
                case 2://普通员工 级别
                    sql = "SELECT * FROM bas_Department Where 1=2 ";//不返回任何部门
                    break;
                default: break;
            }
            
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
            {
                DataRow dr = ds.Tables[0].NewRow();
                dr["DepartmentID"] = DBNull.Value;
                dr["DepartmentName"] = "空";
                ds.Tables[0].Rows.Add(dr);
                return ds.Tables[0];
            }
            else
                return null;
        }
        #endregion

        #region 更新部门信息

        internal static string CreateDepartmentData(DepartmentEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO [bas_Department]([DepartmentName], [MailServer])
                                         VALUES('{0}','{1}');Select SCOPE_IDENTITY();",
                                           entity.DepartmentName,
                                           entity.MailServer);
            return db.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        internal static int UpdateDepartmentData(DepartmentEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [bas_Department]
                                         SET [DepartmentName]='{0}', [MailServer]='{1}'
                                         WHERE DepartmentID={2}",
                                           entity.DepartmentName,
                                           entity.MailServer,
                                           entity.DepartmentID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteDepartmentData(string DepartmentID)
        {
            if (DepartmentID == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Department WHERE DepartmentID={0}", DepartmentID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 获取费用类别信息

        /// <summary>
        /// 生成新的ExpenseTypeID
        /// </summary>
        /// <returns></returns>
        public static int GetNewExpenseTypeID()
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT MAX(ExpenseTypeID)+1 FROM bas_ExpenseType");

            dbCommand = db.GetSqlStringCommand(sql);
            return int.Parse(db.ExecuteScalar(CommandType.Text, sql).ToString());
        }
        /// <summary>
        /// 根据类别获取费用类型
        /// </summary>
        /// <param name="type">1为标准项，2为扩展项，其它值为全部</param>
        /// <returns></returns>
        public static DataTable GetExpenseTypeData(int type)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = (type == 1 || type == 2) ? string.Format("SELECT [ExpenseTypeID], [ExpenseTypeName],[Type] FROM bas_ExpenseType WHERE type={0}", type) : "SELECT [ExpenseTypeID], [ExpenseTypeName],[Type] FROM bas_ExpenseType";

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据费用类型ID获取费用类型
        /// </summary>
        /// <param name="tourgroupid"></param>
        /// <returns></returns>
        public static string GetExpenseTypeNameByID(int expensetypeid)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT ExpenseTypeName FROM bas_ExpenseType WHERE ExpenseTypeID='{0}'", expensetypeid);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand).ToString();
        }
        /// <summary>
        /// 通过费用表判断是否指定的费用类型是否已经被使用
        /// </summary>
        /// <param name="expenseTypeId"></param>
        /// <returns>true已经使用，false没被使用</returns>
        public static bool GetExpenseTypeExistUsage(string expenseTypeId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT COUNT([ExpenseTypeID]) FROM fee_Expense WHERE ExpenseTypeID={0}", expenseTypeId);

            dbCommand = db.GetSqlStringCommand(sql);
            return db.ExecuteScalar(CommandType.Text, sql).ToString() != "0";
        }
        
        #endregion

        #region 更新费用类别信息
        internal static int CreateExpenseTypeData(ExpenseTypeEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO bas_ExpenseType([ExpenseTypeID], [ExpenseTypeName], [Type]) VALUES({0}, '{1}',{2})",
                                           entity.ExpenseTypeID,
                                           entity.ExpenseTypeName,
                                           entity.Type);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int UpdateExpenseTypeData(ExpenseTypeEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE [bas_ExpenseType] SET [ExpenseTypeName]='{0}' WHERE ExpenseTypeID={1}",
                                           entity.ExpenseTypeName,
                                           entity.ExpenseTypeID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        internal static int DeleteExpenseTypeData(string expenseTypeId)
        {
            if (expenseTypeId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_ExpenseType WHERE ExpenseTypeID={0}", expenseTypeId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
	    #endregion

        #endregion

        #region 基础服务信息管理

        #region 更新汽车服务信息

        internal static string CreateVehicleData(VehicleEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO srv_Vehicle([City], [ProviderID], [ProviderName], [VehicleType], [ServiceName], [Price], [ContractPath])
                                         VALUES(@City, @ProviderID, @ProviderName, @VehicleType, @ServiceName, @Price, @ContractPath);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "VehicleType", DbType.String, entity.VehicleType);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);
 
            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateVehicleData(VehicleEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE srv_Vehicle
                           SET [City]=@City, [ProviderID]=@ProviderID,[ProviderName]=@ProviderName, [VehicleType]=@VehicleType, [ServiceName]=@ServiceName, [Price]=@Price, [ContractPath]=@ContractPath 
                           WHERE VehicleID=@VehicleID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "VehicleID", DbType.Int32, entity.VehicleID);
            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "VehicleType", DbType.String, entity.VehicleType);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteVehicleData(string vehicleId)
        {
            if (vehicleId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM srv_Vehicle WHERE VehicleID={0}", vehicleId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        
        #endregion

        #region 更新餐厅服务信息

        internal static string CreateRestaurantData(RestaurantEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO srv_Restaurant([City], [ProviderID], [ProviderName],[ServiceName], [Price1],[Price2],[Price3],[Price4],[Price5],[Price6],[Price7],[Price8],[Price9],[Price10], [AccompanyUnitPrice], [ContractPath])
                                         VALUES(@City, @ProviderID, @ProviderName,@ServiceName, @Price1, @Price2,@Price3,@Price4,@Price5,@Price6,@Price7,@Price8,@Price9,@Price10, @AccompanyUnitPrice, @ContractPath);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price1", DbType.Decimal, entity.Price1);
            db.AddInParameter(dbCommand, "Price2", DbType.Decimal, entity.Price2);
            db.AddInParameter(dbCommand, "Price3", DbType.Decimal, entity.Price3);
            db.AddInParameter(dbCommand, "Price4", DbType.Decimal, entity.Price4);
            db.AddInParameter(dbCommand, "Price5", DbType.Decimal, entity.Price5);
            db.AddInParameter(dbCommand, "Price6", DbType.Decimal, entity.Price6);
            db.AddInParameter(dbCommand, "Price7", DbType.Decimal, entity.Price7);
            db.AddInParameter(dbCommand, "Price8", DbType.Decimal, entity.Price8);
            db.AddInParameter(dbCommand, "Price9", DbType.Decimal, entity.Price9);
            db.AddInParameter(dbCommand, "Price10", DbType.Decimal, entity.Price10);
            db.AddInParameter(dbCommand, "AccompanyUnitPrice", DbType.Decimal, entity.AccompanyUnitPrice);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateRestaurantData(RestaurantEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE srv_Restaurant
                           SET [City]=@City, [ProviderID]=@ProviderID,[ProviderName]=@ProviderName,[ServiceName]=@ServiceName, [Price1]=@Price1,[Price2]=@Price2,[Price3]=@Price3,[Price4]=@Price4,[Price5]=@Price5,[Price6]=@Price6,[Price7]=@Price7,[Price8]=@Price8,[Price9]=@Price9,[Price10]=@Price10, [AccompanyUnitPrice]=@AccompanyUnitPrice, [ContractPath]=@ContractPath 
                           WHERE RestaurantID=@RestaurantID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "RestaurantID", DbType.Int32, entity.RestaurantID);
            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price1", DbType.Decimal, entity.Price1);
            db.AddInParameter(dbCommand, "Price2", DbType.Decimal, entity.Price2);
            db.AddInParameter(dbCommand, "Price3", DbType.Decimal, entity.Price3);
            db.AddInParameter(dbCommand, "Price4", DbType.Decimal, entity.Price4);
            db.AddInParameter(dbCommand, "Price5", DbType.Decimal, entity.Price5);
            db.AddInParameter(dbCommand, "Price6", DbType.Decimal, entity.Price6);
            db.AddInParameter(dbCommand, "Price7", DbType.Decimal, entity.Price7);
            db.AddInParameter(dbCommand, "Price8", DbType.Decimal, entity.Price8);
            db.AddInParameter(dbCommand, "Price9", DbType.Decimal, entity.Price9);
            db.AddInParameter(dbCommand, "Price10", DbType.Decimal, entity.Price10);
            db.AddInParameter(dbCommand, "AccompanyUnitPrice", DbType.Decimal, entity.AccompanyUnitPrice);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteRestaurantData(string restaurantId)
        {
            if (restaurantId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM srv_Restaurant WHERE RestaurantID={0}", restaurantId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 更新演出门票服务信息

        internal static string CreatePlayTicketData(PlayTicketEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO srv_PlayTicket([City], [ProviderID], [ProviderName], [ServiceName], [Price], [ContractPath])
                                         VALUES(@City, @ProviderID, @ProviderName, @ServiceName, @Price, @ContractPath);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdatePlayTicketData(PlayTicketEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE srv_PlayTicket
                           SET [City]=@City, [ProviderID]=@ProviderID,[ProviderName]=@ProviderName, [ServiceName]=@ServiceName, [Price]=@Price, [ContractPath]=@ContractPath 
                           WHERE PlayTicketID=@PlayTicketID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "PlayTicketID", DbType.Int32, entity.PlayTicketID);
            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeletePlayTicketData(string PlayTicketID)
        {
            if (PlayTicketID == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM srv_PlayTicket WHERE PlayTicketID={0}", PlayTicketID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 更新宾馆服务信息

        internal static string CreateHotelData(HotelEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO srv_Hotel([City], [ProviderID], [ProviderName], [FeaturesRoom],[ContractPath])
                                         VALUES(@City, @ProviderID, @ProviderName, @FeaturesRoom, @ContractPath);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "FeaturesRoom", DbType.String, entity.FeaturesRoom);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateHotelData(HotelEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE srv_Hotel
                           SET [City]=@City, [ProviderID]=@ProviderID,[ProviderName]=@ProviderName, [FeaturesRoom]=@FeaturesRoom, [ContractPath]=@ContractPath 
                           WHERE HotelID=@HotelID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "HotelID", DbType.Int32, entity.HotelID);
            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "FeaturesRoom", DbType.String, entity.FeaturesRoom);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteHotelData(string HotelID)
        {
            if (HotelID == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM srv_Hotel WHERE HotelID={0}", HotelID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion

        #region 更新旅行社服务信息

        internal static string CreateTravelAgentData(TravelAgentEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO srv_TravelAgent([City], [ProviderID], [ProviderName], [ServiceName],[Price],[Remark])
                                         VALUES(@City, @ProviderID, @ProviderName, @ServiceName, @Price, @Remark);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "Remark", DbType.String, entity.Remark);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateTravelAgentData(TravelAgentEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE srv_TravelAgent
                           SET [City]=@City, [ProviderID]=@ProviderID,[ProviderName]=@ProviderName, [ServiceName]=@ServiceName,[Price]=@Price,[Remark]=@Remark 
                           WHERE TravelAgentID=@TravelAgentID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "TravelAgentID", DbType.Int32, entity.TravelAgentID);
            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "Remark", DbType.String, entity.Remark);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteTravelAgentData(string TravelAgentID)
        {
            if (TravelAgentID == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM srv_TravelAgent WHERE TravelAgentID={0}", TravelAgentID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #region 更新景点门票服务信息

        internal static string CreateParkTicketData(ParkTicketEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO srv_ParkTicket([City], [ProviderID], [ProviderName], [ServiceName],[OffSeasonPrice],[BusySeasonPrice],[ContractPath])
                                         VALUES(@City, @ProviderID, @ProviderName, @ServiceName, @OffSeasonPrice,@BusySeasonPrice, @ContractPath);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "OffSeasonPrice", DbType.Decimal, entity.OffSeasonPrice);
            db.AddInParameter(dbCommand, "BusySeasonPrice", DbType.Decimal, entity.BusySeasonPrice);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateParkTicketData(ParkTicketEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE srv_ParkTicket
                           SET [City]=@City, [ProviderID]=@ProviderID,[ProviderName]=@ProviderName, [ServiceName]=@ServiceName,[OffSeasonPrice]=@OffSeasonPrice,[BusySeasonPrice]=@BusySeasonPrice,[ContractPath]=@ContractPath 
                           WHERE ParkTicketID=@ParkTicketID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "ParkTicketID", DbType.Int32, entity.ParkTicketID);
            db.AddInParameter(dbCommand, "City", DbType.String, entity.City);
            db.AddInParameter(dbCommand, "ProviderID", DbType.Int32, entity.ProviderID);
            db.AddInParameter(dbCommand, "ProviderName", DbType.String, entity.ProviderName);
            db.AddInParameter(dbCommand, "ServiceName", DbType.String, entity.ServiceName);
            db.AddInParameter(dbCommand, "OffSeasonPrice", DbType.Decimal, entity.OffSeasonPrice);
            db.AddInParameter(dbCommand, "BusySeasonPrice", DbType.Decimal, entity.BusySeasonPrice);
            db.AddInParameter(dbCommand, "ContractPath", DbType.String, entity.ContractPath);

            return db.ExecuteNonQuery(dbCommand);
        }

        internal static int DeleteParkTicketData(string ParkTicketID)
        {
            if (ParkTicketID == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM srv_ParkTicket WHERE ParkTicketID={0}", ParkTicketID);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

        #endregion




    }
}
